 --EXEC sp_lot_trace_by_lot_number_by_type 'K85285','fabLot'
--EXEC sp_lot_trace_by_lot_number_by_type 'SPA826032.1','abLot' 
ALTER  proc sp_lot_trace_by_lot_number_by_type
 @input_lot_number 	varchar(50),
 @lot_Type   		varchar(50)
AS
begin
 
 	declare @results Table( Lot_ID 		bigint,
				Fab_Lot_Number 	varchar(50),
 				Lot_Number 	varchar(50),
 				Part_Number 	varchar(50),
 				PS_Type 	varchar(50),
 				PS_Type_Name 	varchar(50),
 				Vendor_Name 	varchar(50),
 				Wafer_Qty_In 	int,
 				Wafer_Qty_Out 	int,
 				Good_Die 	int,
 				Qty_In 		int,
 				Qty_Out 	int,
 				Yield 		float,
 				Date_In 	datetime,
 				Date_Out 	datetime,
 				Ship_To_Location nvarchar(100),
				Test_Program	varchar(50),
				Lot_Category	varchar(20),
				Lot_Status	varchar(50),
				Hold_Reason	varchar(50),
 				Lot_Comments 	varchar(600),
				Date_code   varchar(50),
				Lot_Split   INT,
				[User_Name] VARCHAR(50),
				Bom_Lot_No	VARCHAR(100),
				BOM_Qty     VARCHAR(50),
				Ps_Type_Id 	int,
				CUST_NAME VARCHAR(100),
 				SALES_ORDER_NO VARCHAR(50),
 				CUST_SHIP_DATE DATETIME,
 				CUST_SHIP_QTY  INT )
 
 	DECLARE @LOT_TRACE_TABLE TABLE (Lot_ID 			INT, 
 					Lot_Number 		VARCHAR(50),
					Fab_Lot_Number 		VARCHAR(50),  
 					ProcessStep_ID  	INT, 
 					Location_ID 		INT, 
 					Ps_Type_Name 		VARCHAR(50), 
					Lot_Split			INT,
 					Lot_Comments 		VARCHAR(600), 
 					Lot_Current_Status 	VARCHAR(20),
					Lot_Category		VARCHAR(20), 	 
 					TimeStamp 	   	DATETIME )
 
 	declare @lot_id_cursor 	 CURSOR
 	declare @shipped_cursor  CURSOR
 	DECLARE @Sales_cursor	CURSOR
	DECLARE @Assembly_cursor  CURSOR
 
	declare @index		 int
	declare @temp_str	 Varchar(50)
	declare @lot_id          int
 	declare @location_id 	 int
 	declare @vendor_name 	 varchar(50)
 	declare @process_step_id bigint
 	declare @ps_type_name 	 varchar(50)
 	declare @ps_type 	 varchar(50)
	declare @lot_category	 varchar(20)
 	declare @qty_in 	 int
 	declare @qty_out 	 int
 	declare @date_in 	 datetime
 	declare @date_out 	 datetime
 	declare @yield 		 float
 	declare @wafer_qty_in 	 int
 	declare @wafer_qty_out 	 int
 	declare @good_die  	 int
	declare @Ps_type_Id      int
	declare @next_location   nvarchar(100)
	declare @test_progarm	 varchar(50)
	declare @fab_lot	 varchar(50)
 
 	declare @part_number 	  varchar(50)
 	declare @actual_ship_date datetime
 	declare @ship_qty 	  int
 	declare @lot_number 	  varchar(50)
	declare @fab_lot_number   varchar(50)
 	declare @lot_comments 	  varchar(500)
 	declare @lot_curr_status  varchar(20)
	declare @hold_reason	  varchar(50)
	declare @date_code	      varchar(50)
	declare @lot_split	      INT
	DECLARE @Assm_rel_Id  INT
	DECLARE @Release_BOM_Lot  VARCHAR (100)
	DECLARE @Release_BOM_Qty  VARCHAR(50)
	DECLARE @user_Name        VARCHAR(50)
 	 
 	DECLARE @CUST_SHIP_LOT_NO VARCHAR(50),@CUST_SHIP_FAB_LOTNO VARCHAR(50), @CUSTMER_NAME 	VARCHAR(100), @SALES_ORDER_NO VARCHAR(50), @CUST_SHIP_DATE DATETIME, @CUST_SHIP_QTY INT 		
 	/*set @index = charindex('.', @input_lot_number)

	if @index <> 0
 	begin
 		set @temp_str = substring(@input_lot_number, 1 , @index )
	end
 
 	if @temp_str is null
 	begin
 		set @temp_str = @input_lot_number
 	end*/ 
	--PRINT @input_lot_number
	SELECT @fab_lot_number = fab_lot_number FROM Lot_TABLE WHERE Lot_Number = @input_lot_number
	IF @fab_lot_number IS NULL
	BEGIN
			SET @fab_lot_number = @input_lot_number
	END

 	INSERT INTO @LOT_TRACE_TABLE
 	SELECT Lot_ID, Lot_Number, Fab_Lot_Number, LT.ProcessStep_ID, Location_ID, Ps_Type_Name, LOT_SPLIT, 
	ISNULL( (CASE WHEN DATALENGTH(Lot_Comments) < 600 THEN Lot_Comments ELSE SUBSTRING( Lot_Comments, 0, 525 )END ), ''),
	Lot_Current_Status, Lot_Category, LT.TIMESTAMP
 	FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID /*AND Lot_Split = 1*/ AND FAB_LOT_NUMBER LIKE @fab_lot_number + '%' 
	AND PS_TYPE_NAME IN ('FAB','WAFER STORE','SORT','Die Bank','Assembly','Test', 'Mark & Pack' ,'Class Store','Finished Goods') 
 		
 	--SELECT *FROM @LOT_TRACE_TABLE	
 	set @lot_id_cursor = CURSOR FOR
 	SELECT Lot_ID, Lot_Number, Fab_Lot_Number, ProcessStep_ID, Location_ID, Ps_Type_Name, LOT_SPLIT, Lot_Comments, Lot_Current_Status, Lot_Category FROM @LOT_TRACE_TABLE ORDER BY TIMESTAMP
 	
 	open @lot_id_cursor
 		fetch next from @lot_id_cursor into @lot_id, @lot_number, @fab_lot, @process_step_id, @location_id, @ps_type_name, @lot_split, @lot_comments, @lot_curr_status, @lot_category
 		
 		while @@fetch_status = 0
 		begin
 			select @ps_type = PS_Type,  @part_number = Part_Number  from Product, Process_Step where Process_Step.ProcessStep_ID = @process_step_id and Process_Step.ProcessStep_ID = Product.ProcessStep_ID
 			select @vendor_name = V_Name from Vendor, Location where Location_ID = @location_id and Location.Vendor_ID = Vendor.Vendor_ID
 			
			SELECT @Ps_type_Id = SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @ps_type
									
 			IF @lot_split < 1
			BEGIN	
					IF @lot_comments IS NOT NULL AND LEN(LTRIM(RTRIM(@lot_comments))) > 0
					BEGIN
						SET @lot_comments = @lot_comments +'<BR> Splited as ' + @lot_number + 'A  and  ' + @lot_number + 'B' 
					END
					ELSE
					BEGIN
						SET @lot_comments = ' Splited as ' + @lot_number + 'A  and  ' + @lot_number + 'B' 
					END
			END
			

 			if @ps_type = 'FAB' OR @ps_type = 'FAB Option'
 			begin
 				
 				select @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table_History where Lot_ID = @lot_id AND Wafer_Qty_In = ( SELECT MAX( Wafer_Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )	

				IF( @qty_in IS NULL OR @qty_in = 0 )				
				BEGIN
					select @qty_in = Wafer_Qty_In, @date_in = Date_In, @date_code= Lot_Date_Code, @user_Name = User_name  from Lot_Table where Lot_ID = @lot_id
				END
				
				select @qty_out = Ship_Qty, @date_out = ISNULL( SL.Ship_Date, SL.TimeStamp ), @next_location = SL_NextLocation from  Shipped_Lot SL where lot_id= @lot_id 
				 					 					
				if ( @lot_curr_status = 'close' and (@qty_out = 0 or @qty_out is null) )
				begin
					set @qty_out = @qty_in
					set @date_out = @date_in				
				end
		
				IF( @qty_in > 0 )
				BEGIN
					
					insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Date_Code, Lot_Split,[User_Name], Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id,@date_code,@lot_split, @user_name,@next_location,@test_progarm, @lot_curr_status, @hold_reason  )
				END
 			end
					
			
			if @ps_type = 'Die Bank'
 			begin
 				select @qty_in = Sum(bin_1), @date_in = MIN(Date_In) , @user_Name = User_name from Lot_Table LT, SORT_Bin_release_table SBRT where LT.LOT_ID = SBRT.LOT_ID AND Lt.Lot_ID = @lot_id Group By User_name

				select  @qty_out = Qty_Out, @date_out = Date_Out  , @date_code = LOT_DATE_CODE ,@user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				
				if ( @qty_out is null OR @qty_out = 0)
				begin
					select @date_out = MAX(art.assembly_release_date), @qty_out = sum(qty_pulled) from assm_release_table art, assm_release_lot_table arlt where arlt.assembly_release_id = art.assembly_release_id and arlt.lot_id = @lot_id and ART.status <> 'Cancelled' AND LOT_Transferred = 0
					If ( @qty_out is null OR @qty_out = 0)
					BEGIN
						--select @date_out = MAX(art.assembly_release_date), @qty_out = sum(qty_pulled) from assm_release_table art, assm_release_lot_table arlt where arlt.assembly_release_id = art.assembly_release_id and arlt.lot_id = @lot_id and ART.status <> 'Cancelled' AND LOT_Transferred = 0
						SELECT @qty_out = SUM(SHIP_QTY) , @date_out = MAX(SHIP_DATE) FROM SHIPPED_LOT where LOT_ID = @lot_id
					END
				end
				--Select @date_out = MAX(Date_In), @qty_out = SUM(Qty_In) From Lot_Table LT, Process_Step PS where LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'Assembly' AND Fab_Lot_Number = @fab_lot 
 				 								 				
 				if ( @qty_in is not null AND @qty_in > 0 )
 				begin
 					insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Date_Code, Lot_Split,[User_Name],Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id,@date_code,@lot_split,@user_name,@next_location,@test_progarm, @lot_curr_status, @hold_reason  )
 				end
 			end	
		
			if @ps_type = 'Assembly' 
 			begin
 				
 				select @qty_in = Qty_In, @date_in = Date_In, @qty_out = Qty_out, @date_out = date_out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table_History where Lot_ID = @lot_id AND Qty_In = ( SELECT MAX( Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )

				IF( @qty_in IS NULL OR @qty_in = 0 )				
				BEGIN
 					select @qty_in = Qty_In, @date_in = Date_In , @date_out = Date_Out, @qty_out = Qty_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				END
				IF( @qty_in > 0 AND (@qty_out IS NULL OR @qty_out = 0 ) )				
				BEGIN
 					select @date_out = Date_Out, @qty_out = Qty_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				END

				DECLARE @bom_lot    VARCHAR(50)
				DECLARE @bom_Qty	VARCHAR(50)
 				select @Assm_rel_Id  = Art.Assembly_release_Id from assm_release_table art, assm_release_lot_table arlt where arlt.assembly_release_id = art.assembly_release_id and arlt.Shipped_Lot_Number = @lot_number and ART.status <> 'Cancelled' 	
				SET @Assembly_cursor = CURSOR FOR SELECT LOT_NUMBER, Qty_Pulled FROM Lot_Table LT, BOM_Release_Lot_table BRLT  WHERE LT.Lot_ID = BRLT.LOT_ID AND assembly_release_id = @Assm_rel_Id
				OPEN @Assembly_cursor
				FETCH NEXT FROM @Assembly_cursor INTO @bom_lot,@bom_Qty
				WHILE @@FETCH_STATUS = 0
				BEGIN
						IF @Release_BOM_Lot IS NULL
						SET @Release_BOM_Lot = @bom_lot
						ELSE
						SET @Release_BOM_Lot = @Release_BOM_Lot + ',' + @bom_lot
						IF @Release_BOM_Qty IS NULL
						SET @Release_BOM_Qty = @bom_Qty
						ELSE
						SET @Release_BOM_Qty = @Release_BOM_Qty + ',' + @bom_Qty
					SET @bom_lot = null 
					SET @bom_Qty = null
					FETCH NEXT FROM @Assembly_cursor INTO @bom_lot,@bom_Qty
				END
				CLOSE @Assembly_cursor
				DEALLOCATE @Assembly_cursor
				/*if ( @qty_out is null OR @qty_out = 0 )
 				begin
					Select @date_out = MIN(Date_In), @qty_out = Sum(Qty_In) From Lot_Table LT, Process_Step PS where LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'Test' AND PS_TYPE = 'FT' AND Fab_Lot_Number = @fab_lot and Lot_id = @lot_id---and Lot_Number = @lot_number
				END*/			

				if ( ( SELECT COUNT(*) FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id ) > 0 )
				begin
					SELECT @qty_in = SL_SCRAPPED_Die_QTY, @date_in = SL_SCRAPPED_DATE, @lot_comments = SL_COMMENTS, @date_code = LOT_DATE_CODE, @user_name = SL_GENERATED_BY FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id
					
					insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, DATE_code,[User_Name])
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101), @lot_category, 'Moved To Scrap',@Ps_type_Id, @date_code,@user_name )
				end
				ELSE
				BEGIN
					IF( @qty_in > 0 )
					BEGIN						
						insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, date_code, Lot_Split,[User_Name],Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @date_code, @lot_split,@user_name,@next_location,@test_progarm, @lot_curr_status, @hold_reason  )
					END
				END
 			END

			if @ps_type = 'FT' 
 			begin
 				
 				select @qty_in = Qty_In, @date_in = Date_In, @qty_out = Qty_out, @date_out = date_out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table_History where Lot_ID = @lot_id AND Qty_In = ( SELECT MAX( Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )

				IF( @qty_in IS NULL OR @qty_in = 0 )				
				BEGIN
 					select @qty_in = Qty_In, @date_in = Date_In , @date_out = Date_Out, @qty_out = Qty_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				END
				IF( @qty_in > 0 AND (@qty_out IS NULL OR @qty_out = 0 ) )				
				BEGIN
 					select @date_out = Date_Out, @qty_out = Qty_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				END
				/*if(@qty_out is null OR @qty_out = 0)
				begin				
					Select @date_out = Date_In, @qty_out = Qty_In From Lot_Table LT, Process_Step PS where LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name In( 'Class Store', 'Mark & Pack') AND Fab_Lot_Number = @fab_lot 

					if(@qty_out is null OR @qty_out = 0)
					begin
						Select @date_out = Date_In, @qty_out = Qty_In From Lot_Table LT, Process_Step PS where LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'Finished Goods' AND Fab_Lot_Number = @fab_lot 
					end	
				END*/

				if ( ( SELECT COUNT(*) FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id ) > 0 )
				begin
					SELECT @qty_in = SL_SCRAPPED_Die_QTY, @date_in = SL_SCRAPPED_DATE, @lot_comments = SL_COMMENTS, @date_code = LOT_DATE_CODE, @user_name = SL_GENERATED_BY FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id
					
					insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id,date_code,[User_Name])
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101), @lot_category, 'Moved To Scrap',@Ps_type_Id,@date_code,@user_name )
				end
				ELSE
				BEGIN
					IF( @qty_in > 0 )
					BEGIN
						insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, date_code, Lot_Split, [User_Name],Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id,@date_code, @lot_split,@user_name, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
					END
				END
 			end
			
			if @ps_type = 'Class Store' or @ps_type = 'Bake & Dry' OR @ps_type = 'Tape & Reel'
 			begin
 				select @qty_in = Qty_In, @date_in = Date_In, @qty_out = Qty_out, @date_out = date_out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table_History where Lot_ID = @lot_id AND Qty_In = ( SELECT MAX( Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )

				IF( @qty_in IS NULL OR @qty_in = 0 )				
				BEGIN
 					select @qty_in = Qty_In, @date_in = Date_In , @date_out = Date_Out, @qty_out = Qty_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				END
				IF( @qty_in > 0 AND (@qty_out IS NULL OR @qty_out = 0 ) )				
				BEGIN
 					select @date_out = Date_Out, @qty_out = Qty_Out,@date_code= Lot_Date_Code, @user_Name = User_name from Lot_Table where Lot_ID = @lot_id
				END
				/*if ( @qty_out is null OR @qty_out = 0 )
 				begin
					Select @date_out = Date_In, @qty_out = Qty_In From Lot_Table LT, Process_Step PS where LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'Finished Goods' AND Fab_Lot_Number = @fab_lot_number	
 				END */
				 								 				
 				if ( @qty_in is not null AND @qty_in > 0 )
 				begin
 					insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, date_code, Lot_Split,[User_Name], Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id,@date_code,@lot_split, @user_name,@next_location,@test_progarm, @lot_curr_status, @hold_reason  )
 				end
 			end
			
			if @ps_type = 'FG'
 			begin
 				select @qty_in = Qty_In, @date_in = Date_In , @date_out = Date_Out, @qty_out = Qty_Out, @date_code = LOT_DATE_CODE, @user_Name = User_name  from Lot_Table where Lot_ID = @lot_id
				SELECT @ship_qty = SUM(CSLNT_QTY_PULL) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE WHERE LOT_ID = @lot_id AND CSLNT_STATUS = 'SHIPPED'
 			
 				IF ( @qty_in IS NULL OR @qty_in = 0 ) 
 				BEGIN
 					SET  @qty_in = isNUll(@ship_qty,0) + isNull(@qty_out, 0)
 				END
 				ELSE
 				BEGIN
 					SET @qty_in = isNull(@qty_in,0) + isNull(@ship_qty,0)
 				END

				SET @qty_out = isNull(@ship_qty,0) + isNull(@qty_out,0)

 				/*if( @qty_in is null or @qty_in = 0 ) 
 				begin
 					select top 1 @qty_in = Qty_In, @date_in = Date_In from Lot_Table_History where Lot_ID = @lot_id order by timestamp desc
 				end
				 					 					
				if ( @lot_curr_status = 'close' and (@qty_out = 0 or @qty_out is null) )
				begin
					set @qty_out = @qty_in
					set @date_out = @date_in				
				end*/
 				 								 				
 				if ( @qty_in is not null AND @qty_in > 0 )
 				begin
 					insert @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, date_code,Lot_Split,[User_Name], Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
						values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(varchar, @date_in, 101), convert(varchar, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id,@date_code, @lot_split,@user_name,@next_location,@test_progarm, @lot_curr_status, @hold_reason  )
 				end
 						
 			end
			---FOR GETTING THE SALES ORDER , CUSTOMER INFO
 			IF @ps_type = 'FG' OR @Ps_type = 'Finished Goods'
 			BEGIN
					SET @Sales_cursor = CURSOR FOR SELECT LOT_NUMBER,FAB_LOT_NUMBER, C_NAME, SALESORDER_NO, CST_SHIP_DATE, SUM(CSLNT_QTY_PULL), LT.LOT_DATE_CODE  FROM CUSTOMER_SHIPPING_TABLE CST , CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLT, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, SALES_ORDER SO, CUSTOMER C, LOT_TABLE LT
 						WHERE CSLT.CSLIT_ID= CSLIT.CSLIT_ID AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = C.CUSTOMER_ID AND LT.LOT_ID = CSLT.LOT_ID AND CSLT.LOT_ID = @lot_id AND CST.CST_STATUS = 'SHIPPED' AND CSLNT_QTY_PULL > 0 GROUP BY LOT_NUMBER, FAB_LOT_NUMBER, C_NAME, SALESORDER_NO,CST_SHIP_DATE,LOT_DATE_CODE
 					OPEN @Sales_cursor
 					FETCH NEXT FROM @Sales_cursor INTO @CUST_SHIP_LOT_NO, @CUST_SHIP_FAB_LOTNO, @CUSTMER_NAME, @SALES_ORDER_NO, @CUST_SHIP_DATE,@CUST_SHIP_QTY , @DATE_CODE
 					WHILE @@FETCH_STATUS = 0
 					BEGIN
 						INSERT @results ( Fab_Lot_Number,LOT_NUMBER, CUST_NAME, SALES_ORDER_NO, CUST_SHIP_DATE,CUST_SHIP_QTY, Ps_Type_Id,DATE_CODE)
 							values( @CUST_SHIP_FAB_LOTNO,@CUST_SHIP_LOT_NO, @CUSTMER_NAME, @SALES_ORDER_NO, CONVERT(VARCHAR, @CUST_SHIP_DATE,101), @CUST_SHIP_QTY, 20, @DATE_CODE )
	 
 						SET @CUSTMER_NAME		= null
 						SET @SALES_ORDER_NO		= null
 						SET @CUST_SHIP_DATE     = NULL
 						SET @CUST_SHIP_QTY		= NULL
						SET @CUST_SHIP_LOT_NO	= NULL
						SET @CUST_SHIP_FAB_LOTNO = NULL
						SET @DATE_CODE			 = null
 						FETCH NEXT FROM @Sales_cursor INTO @CUST_SHIP_LOT_NO, @CUST_SHIP_FAB_LOTNO,@CUSTMER_NAME, @SALES_ORDER_NO, @CUST_SHIP_DATE,@CUST_SHIP_QTY , @DATE_CODE
 					END
 					CLOSE @Sales_cursor
 					DEALLOCATE @Sales_cursor
	 				
 			END
  			
 			set @lot_id 		= null
 			set @process_step_id 	= null
 			set @location_id 	= null
 			set @vendor_name 	= null
 			set @ps_type_name 	= null
 			set @ps_type 		= null
 			set @qty_in 		= null
 			set @qty_out 		= null
 			set @date_in 		= null
 			set @date_out 		= null
 			set @wafer_qty_in 	= null
 			set @wafer_qty_out 	= null
 			set @good_die 		= null
 			set @part_number 	= null
			set @lot_category	= null
 			set @lot_comments 	= null
 			set @lot_curr_status 	= null
			set @Ps_type_Id	     	= null
			set @hold_reason	= null
			set @next_location	= null
			set @test_progarm	= null
 			SET @ship_qty		= null				
			SET @Release_BOM_Lot = NULL
			SET @Release_BOM_Qty = null	
			SET @DATE_CODE		= null
			SET @lot_split		= null
			SET @User_Name		= null	
			SET @Assm_rel_Id	= null
 			fetch next from @lot_id_cursor into @lot_id, @lot_number,@fab_lot, @process_step_id, @location_id, @ps_type_name, @lot_split, @lot_comments, @lot_curr_status, @lot_category
 		end

 	close @lot_id_cursor
 	deallocate @lot_id_cursor

	--select * from @results

 	if @lot_Type = 'fabLot'
	begin
		select LOT_ID,Fab_Lot_Number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, SUM(Wafer_Qty_In) as Wafer_Qty_In,  SUM(Wafer_Qty_Out) as Wafer_Qty_Out, SUM(Qty_In) as Qty_In, SUM(Qty_Out) as Qty_Out, Yield, min(Date_In) as Date_In , max(Date_Out) as Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Date_Code, ISNULL(Lot_Split,1) AS Lot_Split,[User_Name],Bom_Lot_No,Bom_Qty,Ship_To_Location,Test_Program,Lot_Status, Hold_Reason, Good_Die ,CUST_NAME,SALES_ORDER_NO,CUST_SHIP_DATE,CUST_SHIP_QTY
			from @results group by LOT_ID,fab_lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name,Yield, Lot_Category, Lot_Comments, Date_Code, Lot_Split,[User_Name],Bom_Lot_No,Bom_Qty,Ps_Type_Id, Ship_To_Location , Test_Program, Lot_Status, Hold_Reason, Good_Die,CUST_NAME,SALES_ORDER_NO,CUST_SHIP_DATE,CUST_SHIP_QTY order by Ps_Type_Id --,Lot_Number--,Date_In
	end
	else --if @lot_Type = 'vendorLot'
	begin
		select LOT_ID,Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, Vendor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In, Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Date_Code, ISNULL(Lot_Split,1) AS Lot_Split, [User_Name],Bom_Lot_No,Bom_Qty,Ship_To_Location,Test_Program,Lot_Status, Hold_Reason, Good_Die ,CUST_NAME,SALES_ORDER_NO,CUST_SHIP_DATE,CUST_SHIP_QTY
			from @results order by Ps_Type_Id,Lot_Number--,Date_In
	end
	--select * from @results order by Ps_Type_Id,Lot_Number--,Date_In
 	
 end


